package org.catspaw.cherubim.persistence.dao.spring.jdbc;

import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.catspaw.cherubim.persistence.dao.ExtendedDao;
import org.catspaw.cherubim.persistence.dao.jdbc.SqlUtils;
import org.catspaw.cherubim.persistence.jdbc.PageSqlHelper;
import org.catspaw.cherubim.persistence.jdbc.SuitableParameterUtils;
import org.catspaw.cherubim.persistence.mapping.DefaultEntityMappingResolver;
import org.catspaw.cherubim.persistence.mapping.EntityMappingResolver;
import org.catspaw.cherubim.persistence.mapping.EntityMappingUtils;
import org.catspaw.cherubim.persistence.spring.jdbc.NameTrimmedBeanRowMapper;
import org.catspaw.cherubim.persistence.spring.jdbc.ScalarRowMapper;
import org.catspaw.cherubim.persistence.spring.jdbc.SuitableParameterBatchPreparedStatementSetter;
import org.catspaw.cherubim.persistence.spring.jdbc.SuitableParameterPreparedStatementSetter;
import org.catspaw.cherubim.util.GenericTypeUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public abstract class BaseSpringJdbcDao<E, P> extends JdbcDaoSupport implements
		ExtendedDao<E, P> {

	private EntityMappingResolver	entityMappingResolver;
	private Class<E>				entityClass;			//dao所持久化的实体class
	private Class<P>				idClass;
	private PageSqlHelper			pageSqlHelper;

	public BaseSpringJdbcDao(PageSqlHelper pageSqlHelper) {
		this.pageSqlHelper = pageSqlHelper;
	}

	private void initEntityMappingResolver() {
		if (entityMappingResolver == null) {
			entityMappingResolver = createEntityMappingResolver();
		}
	}

	protected EntityMappingResolver createEntityMappingResolver() {
		return new DefaultEntityMappingResolver(getEntityClass());
	}

	/**
	 * 取得entityClass的函数. JDK1.4不支持泛型的子类可以抛开Class<E> entityClass, 重新实现此函数达到相同效果。
	 */
	protected Class<E> getEntityClass() {
		if (entityClass == null) {
			entityClass = (Class<E>) GenericTypeUtils
					.getSuperClassGenericType(this.getClass());
		}
		return entityClass;
	}

	protected void setEntityClass(Class<E> entityClass) {
		this.entityClass = entityClass;
	}

	@SuppressWarnings("unchecked")
	protected E[] createEmptyEntityArray(int length) {
		return (E[]) Array.newInstance(getEntityClass(), length);
	}

	protected Class<P> getIdClass() {
		if (idClass == null) {
			idClass = (Class<P>) GenericTypeUtils.getSuperClassGenericType(this
					.getClass(), 1);
		}
		return idClass;
	}

	protected void setIdClass(Class<P> idClass) {
		this.idClass = idClass;
	}

	protected String getIdFieldName() {
		return entityMappingResolver.getIdProperty();
	}

	protected String getTableName() {
		return entityMappingResolver.getTable();
	}

	public E create() {
		try {
			return getEntityClass().newInstance();
		} catch (InstantiationException e) {
			throw new IllegalStateException(e);
		} catch (IllegalAccessException e) {
			throw new IllegalStateException(e);
		}
	}

	public Number total() {
		initEntityMappingResolver();
		String sql = SqlUtils.generateTotalSql(entityMappingResolver);
		return queryForNumber(sql);
	}

	private Number queryForNumber(String sql) {
		return (Number) getJdbcTemplate().queryForObject(sql, Number.class);
	}

	public E get(P id) {
		initEntityMappingResolver();
		String sql = SqlUtils.generateGetSql(entityMappingResolver);
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				new Object[] { id });
		List list = getJdbcTemplate().query(
											sql,
											setter,
											new NameTrimmedBeanRowMapper(
													getEntityClass()));
		if (!list.isEmpty()) {
			return (E) list.get(0);
		}
		return null;
	}

	public void save(E entity) {
		initEntityMappingResolver();
		final String saveSql = SqlUtils.generateSaveSql(entityMappingResolver);
		String[] properties = entityMappingResolver.getProperties();
		final Object[] params = EntityMappingUtils
				.getPropertyValues(entity, properties);
		PreparedStatementCreator creator = new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(saveSql);
				for (int i = 0; i < params.length; i++) {
					SuitableParameterUtils.setSuitedParameter(ps, i + 1,
																params[i]);
				}
				return ps;
			}
		};
		getJdbcTemplate().update(creator);
	}

	public P saveAndObtainId(E entity) {
		initEntityMappingResolver();
		final String saveSql = SqlUtils.generateSaveSql(entityMappingResolver);
		String[] properties = entityMappingResolver.getProperties();
		final Object[] params = EntityMappingUtils
				.getPropertyValues(entity, properties);
		PreparedStatementCreator creator = new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(saveSql);
				for (int i = 0; i < params.length; i++) {
					SuitableParameterUtils.setSuitedParameter(ps, i + 1,
																params[i]);
				}
				return ps;
			}
		};
		P id = (P) EntityMappingUtils
				.getPropertyValue(entity, getIdFieldName());
		if (id == null) {
			KeyHolder keyHolder = new GeneratedKeyHolder();
			getJdbcTemplate().update(creator, keyHolder);
			id = (P) keyHolder.getKey();
		} else {
			getJdbcTemplate().update(creator);
		}
		return id;
	}

	public void saveAll(E[] entities) {
		initEntityMappingResolver();
		String saveAllSql = SqlUtils.generateSaveSql(entityMappingResolver);
		Object[][] params = new Object[entities.length][];
		for (int i = 0; i < entities.length; i++) {
			E entity = entities[i];
			String[] properties = entityMappingResolver.getProperties();
			params[i] = EntityMappingUtils
					.getPropertyValues(entity, properties);
		}
		BatchPreparedStatementSetter setter = new SuitableParameterBatchPreparedStatementSetter(
				params);
		getJdbcTemplate().batchUpdate(saveAllSql, setter);
	}

	public void saveAll(Collection<E> col) {
		E[] entities = col.toArray(createEmptyEntityArray(col.size()));
		saveAll(entities);
	}

	public void delete(E entity) {
		initEntityMappingResolver();
		String sql = SqlUtils.generateDeleteSql(entityMappingResolver);
		String idProperty = entityMappingResolver.getProperty(getIdFieldName());
		Object id = EntityMappingUtils.getPropertyValue(entity, idProperty);
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				new Object[] { id });
		getJdbcTemplate().update(sql, setter);
	}

	public void deleteAll(Collection<E> entities) {
		initEntityMappingResolver();
		String sql = SqlUtils.generateDeleteSql(entityMappingResolver);
		for (E entity : entities) {
			String idProperty = entityMappingResolver
					.getProperty(getIdFieldName());
			Object id = EntityMappingUtils.getPropertyValue(entity, idProperty);
			PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
					new Object[] { id });
			getJdbcTemplate().update(sql, setter);
		}
	}

	public void deleteAll(E[] entities) {
		initEntityMappingResolver();
		String sql = SqlUtils.generateDeleteSql(entityMappingResolver);
		for (int i = 0; i < entities.length; i++) {
			String idProperty = entityMappingResolver
					.getProperty(getIdFieldName());
			Object id = EntityMappingUtils.getPropertyValue(entities[i],
															idProperty);
			PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
					new Object[] { id });
			getJdbcTemplate().update(sql, setter);
		}
	}

	public void deleteByProperty(String name, Object value) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("delete from ");
		builder.append(getTableName());
		builder.append(" where ");
		builder.append(entityMappingResolver.getColumn(name));
		builder.append("=?");
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				new Object[] { value });
		getJdbcTemplate().update(builder.toString(), setter);
	}

	public void deleteInProperties(String name, Object[] value) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("delete from ");
		builder.append(getTableName());
		builder.append(" where ");
		builder.append(entityMappingResolver.getColumn(name));
		if (value == null || value.length < 1) {
			builder.append(" is null ");
		} else if (value.length < 2) {
			builder.append("=?");
		} else {
			builder.append(" in(");
			for (int i = 0; i < value.length; i++) {
				builder.append("?");
				if (i < value.length - 1) {
					builder.append(",");
				}
			}
			builder.append(") ");
		}
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				new Object[] { value });
		getJdbcTemplate().update(builder.toString(), setter);
	}

	public void deleteByProperties(String[] names, Object[] values) {
		if (names.length != values.length) {
			throw new IllegalArgumentException(
					"the length of names and values are not equal!");
		}
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("delete from ");
		builder.append(getTableName());
		builder.append(" where ");
		for (int i = 0; i < names.length; i++) {
			String name = entityMappingResolver.getColumn(names[i]);
			builder.append(name);
			if (values[i] != null) {
				builder.append(" =?");
			} else {
				builder.append(" is null");
			}
			if (i < names.length - 1) {
				builder.append(" and ");
			}
		}
		String sql = builder.toString();
		List<Object> valueList = new ArrayList<Object>();
		for (int i = 0; i < values.length; i++) {
			if (values[i] != null) {
				valueList.add(values[i]);
			}
		}
		values = valueList.toArray();
		getJdbcTemplate()
				.update(sql,
						new SuitableParameterPreparedStatementSetter(values));
	}

	public void update(E entity) {
		initEntityMappingResolver();
		String updateSql = SqlUtils.generateUpdateSql(entityMappingResolver);
		String[] properties = entityMappingResolver.getProperties();
		Object[] params = EntityMappingUtils.getPropertyValues(entity,
																properties);
		getJdbcTemplate()
				.update(updateSql,
						new SuitableParameterPreparedStatementSetter(params));
	}

	public void updateAll(E[] entities) {
		initEntityMappingResolver();
		String updateSql = SqlUtils.generateUpdateSql(entityMappingResolver);
		Object[][] params = new Object[entities.length][];
		String[] properties = entityMappingResolver.getProperties();
		for (int i = 0; i < entities.length; i++) {
			params[i] = EntityMappingUtils.getPropertyValues(entities[i],
																properties);
		}
		BatchPreparedStatementSetter setter = new SuitableParameterBatchPreparedStatementSetter(
				params);
		getJdbcTemplate().batchUpdate(updateSql, setter);
	}

	public void updateAll(Collection<E> entities) {
		initEntityMappingResolver();
		String updateSql = SqlUtils.generateUpdateSql(entityMappingResolver);
		Object[][] params = new Object[entities.size()][];
		String[] properties = entityMappingResolver.getProperties();
		int i = 0;
		for (E entity : entities) {
			params[i] = EntityMappingUtils
					.getPropertyValues(entity, properties);
			i++;
		}
		BatchPreparedStatementSetter setter = new SuitableParameterBatchPreparedStatementSetter(
				params);
		getJdbcTemplate().batchUpdate(updateSql, setter);
	}

	public List<E> findAll() {
		initEntityMappingResolver();
		String sql = SqlUtils.generateFindAllSql(entityMappingResolver);
		return getJdbcTemplate().query(
										sql,
										new NameTrimmedBeanRowMapper(
												getEntityClass()));
	}

	public List<E> findByProperty(String name, Object value) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select * from ");
		builder.append(getTableName());
		builder.append(" where ");
		builder.append(entityMappingResolver.getColumn(name));
		if (value != null) {
			builder.append(" =?");
		} else {
			builder.append(" is null");
		}
		String sql = builder.toString();
		if (value != null) {
			PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
					new Object[] { value });
			return getJdbcTemplate().query(
											sql,
											setter,
											new NameTrimmedBeanRowMapper(
													getEntityClass()));
		} else {
			return getJdbcTemplate().query(
											sql,
											new NameTrimmedBeanRowMapper(
													getEntityClass()));
		}
	}

	public List<E> findByProperty(String name, Object value, int offset, int max) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select * from ");
		builder.append(getTableName());
		builder.append(" where ");
		builder.append(entityMappingResolver.getColumn(name));
		if (value != null) {
			builder.append(" =?");
		} else {
			builder.append(" is null");
		}
		String sql = builder.toString();
		sql = pageSqlHelper.createLimitSql(sql, offset, max);
		Object[] params = pageSqlHelper.createPageParams(value);
		if (value != null) {
			PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
					params);
			return getJdbcTemplate().query(
											sql,
											setter,
											new NameTrimmedBeanRowMapper(
													getEntityClass()));
		} else {
			return getJdbcTemplate().query(
											sql,
											new NameTrimmedBeanRowMapper(
													getEntityClass()));
		}
	}

	public List<E> findInProperties(String name, Object[] value) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select * from ");
		builder.append(getTableName());
		builder.append(" where ");
		builder.append(entityMappingResolver.getColumn(name));
		if (value == null || value.length < 1) {
			builder.append(" is null ");
		} else if (value.length < 2) {
			builder.append("=?");
		} else {
			builder.append(" in(");
			for (int i = 0; i < value.length; i++) {
				builder.append("?");
				if (i < value.length - 1) {
					builder.append(",");
				}
			}
			builder.append(") ");
		}
		String sql = builder.toString();
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				value);
		return getJdbcTemplate().query(
										sql,
										setter,
										new NameTrimmedBeanRowMapper(
												getEntityClass()));
	}

	public List<E> findInProperties(String name, Object[] value, int offset,
			int max) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select * from ");
		builder.append(getTableName());
		builder.append(" where ");
		builder.append(entityMappingResolver.getColumn(name));
		if (value == null || value.length < 1) {
			builder.append(" is null ");
		} else if (value.length < 2) {
			builder.append("=?");
		} else {
			builder.append(" in(");
			for (int i = 0; i < value.length; i++) {
				builder.append("?");
				if (i < value.length - 1) {
					builder.append(",");
				}
			}
			builder.append(") ");
		}
		String sql = builder.toString();
		sql = pageSqlHelper.createLimitSql(sql, offset, max);
		Object[] params = pageSqlHelper.createPageParams(value);
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				params);
		return getJdbcTemplate().query(
										sql,
										setter,
										new NameTrimmedBeanRowMapper(
												getEntityClass()));
	}

	public List<E> findByProperties(String[] names, Object[] values) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select * from ");
		builder.append(getTableName());
		builder.append(" where ");
		for (int i = 0; i < names.length; i++) {
			String name = entityMappingResolver.getColumn(names[i]);
			builder.append(name);
			if (values[i] != null) {
				builder.append(" =?");
			} else {
				builder.append(" is null");
			}
			if (i < names.length - 1) {
				builder.append(" and ");
			}
		}
		String sql = builder.toString();
		Object[] params = pageSqlHelper.createPageParams(values);
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				params);
		return getJdbcTemplate().query(
										sql,
										setter,
										new NameTrimmedBeanRowMapper(
												getEntityClass()));
	}

	public List<E> findByProperties(String[] names, Object[] values,
			int offset, int max) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select * from ");
		builder.append(getTableName());
		builder.append(" where ");
		for (int i = 0; i < names.length; i++) {
			String name = entityMappingResolver.getColumn(names[i]);
			builder.append(name);
			if (values[i] != null) {
				builder.append(" =?");
			} else {
				builder.append(" is null");
			}
			if (i < names.length - 1) {
				builder.append(" and ");
			}
		}
		String sql = builder.toString();
		sql = pageSqlHelper.createLimitSql(sql, offset, max);
		Object[] params = pageSqlHelper.createPageParams(values);
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				params);
		return getJdbcTemplate().query(
										sql,
										setter,
										new NameTrimmedBeanRowMapper(
												getEntityClass()));
	}

	public E findByUnique(String name, Object value) {
		List<E> list = findByProperty(name, value);
		if (!list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}

	public List<E> find(int offset, int max) {
		initEntityMappingResolver();
		String sql = SqlUtils.generateFindAllSql(entityMappingResolver);
		sql = pageSqlHelper.createLimitSql(sql, offset, max);
		List list = getJdbcTemplate().query(
											sql,
											new NameTrimmedBeanRowMapper(
													getEntityClass()));
		return list;
	}

	public Number countByProperty(String name, Object value) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select count(1) from ");
		builder.append(getTableName());
		builder.append(" where ");
		builder.append(entityMappingResolver.getColumn(name));
		if (value != null) {
			builder.append(" =?");
		} else {
			builder.append(" is null");
		}
		String sql = builder.toString();
		if (value != null) {
			PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
					new Object[] { value });
			return (Number) getJdbcTemplate().query(sql, setter,
													ScalarRowMapper.INSTANCE)
					.get(0);
		} else {
			return queryForNumber(sql);
		}
	}

	public Number countInProperties(String name, Object[] value) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select count(1) from ");
		builder.append(getTableName());
		builder.append(" where ");
		builder.append(entityMappingResolver.getColumn(name));
		if (value == null || value.length < 1) {
			builder.append(" is null ");
		} else if (value.length < 2) {
			builder.append("=?");
		} else {
			builder.append(" in(");
			for (int i = 0; i < value.length; i++) {
				builder.append("?");
				if (i < value.length - 1) {
					builder.append(",");
				}
			}
			builder.append(") ");
		}
		String sql = builder.toString();
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				value);
		return (Number) getJdbcTemplate().query(sql, setter,
												ScalarRowMapper.INSTANCE)
				.get(0);
	}

	public Number countByProperties(String[] names, Object[] values) {
		initEntityMappingResolver();
		StringBuilder builder = new StringBuilder("select count(*) from ");
		builder.append(getTableName());
		builder.append(" where ");
		for (int i = 0; i < names.length; i++) {
			String name = entityMappingResolver.getColumn(names[i]);
			builder.append(name);
			if (values[i] != null) {
				builder.append(" =?");
			} else {
				builder.append(" is null");
			}
			if (i < names.length - 1) {
				builder.append(" and ");
			}
		}
		String sql = builder.toString();
		List<Object> valueList = new ArrayList<Object>();
		for (int i = 0; i < values.length; i++) {
			if (values[i] != null) {
				valueList.add(values[i]);
			}
		}
		values = valueList.toArray();
		PreparedStatementSetter setter = new SuitableParameterPreparedStatementSetter(
				values);
		return (Number) getJdbcTemplate().query(sql, setter,
												ScalarRowMapper.INSTANCE)
				.get(0);
	}

	public List<E> findByExample(E entity) {
		throw new UnsupportedOperationException("unsupported operation...");
	}

	protected EntityMappingResolver getEntityMappingResolver() {
		return entityMappingResolver;
	}

	protected PageSqlHelper getPageSqlHelper() {
		return pageSqlHelper;
	}
}
